package com.mdp.lcode.service;


import com.alibaba.fastjson.JSON;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.mdp.core.SpringContextUtils;
import com.mdp.core.entity.Result;
import com.mdp.core.err.BizException;
import com.mdp.core.query.QueryTools;
import com.mdp.core.service.BaseService;
import com.mdp.core.utils.BaseUtils;
import com.mdp.core.utils.DateUtils;
import com.mdp.core.utils.ObjectTools;
import com.mdp.dev.dao.CodeGenDao;
import com.mdp.dev.entity.FieldInfo;
import com.mdp.lcode.entity.DbTable;
import com.mdp.lcode.entity.FormData;
import com.mdp.lcode.entity.FormDef;
import com.mdp.lcode.entity.FormField;
import com.mdp.lcode.mapper.DbTableMapper;
import com.mdp.meta.client.entity.ItemVo;
import com.mdp.meta.client.entity.ExtInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Service
public class DbTableService {

    @Autowired
    DbTableMapper baseMapper;

    @Autowired
    CodeGenDao codeGenDao;

    DynamicRoutingDataSource dataSourceService;


    List<String> getPrimaryKeys(String ds,String tableName){
       return codeGenDao.getPrimaryKeys(ds,tableName);
    }

    void createTable(FormDef formDef, List<FormField> formFields){
        if("3".equals(formDef.getCtType())){
            return;
        }
        if(dataSourceService==null){
            dataSourceService= SpringContextUtils.getBean(DynamicRoutingDataSource.class);
        }
        DataSource dataSource=dataSourceService.getDataSource(formDef.getTableOwner());
        JdbcTemplate jdbcTemplate=new JdbcTemplate();
        jdbcTemplate.setDataSource(dataSource);
        StringBuffer sqlBuff=new StringBuffer();
        if(formDef.getTableName().indexOf(formDef.getBranchId())<0){
            formDef.setTableName(formDef.getBranchId()+"_"+formDef.getTableName());
        }
        //如果没有则创建
        if("2".equals(formDef.getCtType())){
            List<FieldInfo> cols=codeGenDao.getColumnsInfo(formDef.getTableOwner(),formDef.getTableName());
            if(cols!=null && cols.size()>0){
                return;
            }
        }
        String tableName=formDef.getTableName().replaceAll("-","").replaceAll("@","").replaceAll("$","");
        formDef.setTableName(tableName);
        //先删除后创建
        if("1".equals(formDef.getCtType())){
            List<Map<String,Object>> datas=jdbcTemplate.queryForList("select * from " +formDef.getTableName() +" limit 0,1");
            if(datas!=null && datas.size()>0){
                throw new BizException("table-has-data-not-allow-drop","表格%s中存在数据，为安全起见，不能drop表重建，请先清理数据后再继续",formDef.getTableName());
            }
            sqlBuff.append(" DROP TABLE IF EXISTS "+formDef.getTableName());
            jdbcTemplate.update(sqlBuff.toString());
        }
        sqlBuff=new StringBuffer();
        sqlBuff.append(" create table ").append(formDef.getTableName()).append(" (");
        for (FormField f : formFields) {
            Map<String,ExtInfo> extInfoMap= ItemVo.parseExtInfosMap(f.getExtInfos());
            sqlBuff.append(" "+f.getId()).append(" "+getDBTypeByFieldType(f,extInfoMap)).append(" COMMENT '"+f.getRemark()+"',");
        }
        if(ObjectTools.isNotEmpty(formDef.getPks())){
            sqlBuff.append(" PRIMARY KEY ("+formDef.getPks()+") USING BTREE, ");
        }
        String sql=sqlBuff.toString();
        if(sql.endsWith(",")){
            sql=sql.substring(0,sql.length()-1);
        }
        sql=sql+") COMMENT = '"+formDef.getFormName()+"'";
        jdbcTemplate.update(sql);
    }

    private String getDBTypeByFieldType(FormField f, Map<String, ExtInfo> extInfoMap) {

        if(ObjectTools.isEmpty(f.getTyp())){
            if("user".equals(f.getExtType())){
                return "VARCHAR("+f.getLen()+")";
            }else if("dept".equals(f.getExtType())){
                return "VARCHAR("+f.getLen()+")";
            }else if("image".equals(f.getExtType())){
                return "VARCHAR("+f.getLen()+")";
            }else if("json".equals(f.getExtType())){
                return "VARCHAR("+f.getLen()+")";
            }else if("att".equals(f.getExtType())){
                return "VARCHAR("+f.getLen()+")";
            }else if("link".equals(f.getExtType())){
                return "VARCHAR("+f.getLen()+")";
            }else if("tag".equals(f.getExtType())){
                return "VARCHAR("+f.getLen()+")";
            }else if("cate".equals(f.getExtType())){
                return "VARCHAR("+f.getLen()+")";
            }else if("html".equals(f.getExtType())){
                return "VARCHAR("+f.getLen()+")";
            }else if("text".equals(f.getTyp())){
                return "VARCHAR("+f.getLen()+")";
            } else if("datetime".equals(f.getTyp())){
                return " datetime ";
            }else if("date".equals(f.getTyp())){
                return " date ";
            }else if("month".equals(f.getTyp())){
                return "VARCHAR("+f.getLen()+")";
            }else if("year".equals(f.getTyp())){
                return "VARCHAR("+f.getLen()+")";
            }else if("number".equals(f.getTyp())){
                ExtInfo scale=extInfoMap.get("scale");
                if(scale==null && f.getLen()<=8){
                    return "int";
                }else{
                    return "decimal("+f.getLen()+","+scale.getValue()+")";
                }
            }else {
                return "VARCHAR("+f.getLen()+")";
            }
        }else{
            return "VARCHAR("+f.getLen()+")";
        }

    }

    public void saveOrUpdate(FormDef formDef,List<FormField> formFields,Map<String,Object> params){
        if(ObjectTools.isEmpty(formDef.getPks())){
            throw new BizException("pks-required","表未设置主键，不能进行数据维护");
        }
        if(dataSourceService==null){
            dataSourceService= SpringContextUtils.getBean(DynamicRoutingDataSource.class);
        }
        JdbcTemplate template=new JdbcTemplate();
        template.setDataSource(dataSourceService.getDataSource(formDef.getTableOwner()));
        DynamicDataSourceContextHolder.push(formDef.getTableOwner());
        QueryWrapper<DbTable> qw=new QueryWrapper<>();
        for (String key : formDef.getPks().split(",")) {
            Object val=params.get(ObjectTools.camelName(key));
            qw.eq(BaseUtils.underscoreName(key),val);
        }
        IPage page=QueryTools.initPage(BaseUtils.map("pageNum",1,"pageSize",1));
        params.put("tableName",formDef.getTableName());
        List<Map<String,Object>> datas=baseMapper.selectListMapByWhere(page,qw,params);
        DynamicDataSourceContextHolder.poll();
        if(datas!=null && datas.size()>0){//update
            if("2".equals(formDef.getDataType())){//寄存主表
                StringBuffer sb=new StringBuffer();
                List<FormField> fields= FormDefService.calcDbFields(formFields);
                String dataField=ObjectTools.camelToUnderline(formDef.getDataField());
                String dataFieldCamle=ObjectTools.camelName(formDef.getDataField());
                sb.append("update ");
                sb.append(formDef.getTableName());
                sb.append(" set "+dataField).append(" = ? ");
                sb.append(" where 1=1");
                List<Object> vars=new ArrayList<>();
                if(ObjectTools.isEmpty(formDef.getPks())){
                    throw new BizException("pks-not-setting","没有设置主键，无法插入数据");
                }
                for (String k : formDef.getPks().split(",")) {
                    sb.append(" and "+ObjectTools.camelToUnderline(k)+"=?");
                    Object d=params.get(ObjectTools.camelName(k));
                    vars.add(ObjectTools.isEmpty(d)?"":d);
                }
                if(vars.size()==0){
                    throw new BizException("pks-not-setting","没有设置主键，无法更新数据");
                }
                vars.add(0, params.get(dataFieldCamle));
                template.update(sb.toString(),vars.toArray());
            }else if("3".equals(formDef.getDataType())){//独立建表
                StringBuffer sb=new StringBuffer();
                List<FormField> fields= FormDefService.calcDbFields(formFields);
                sb.append("update ");
                sb.append(formDef.getTableName());
                sb.append(" set ");
                List<Object> vars=new ArrayList<>();
                for (int i = 0; i < fields.size(); i++) {
                    FormField field=fields.get(i);
                    Object dval=params.get(field.getIdCamel());
                    if(ObjectTools.isNotEmpty(dval)){
                        sb.append(field.getId()+"=?,");
                        vars.add(dval);
                    }
                }

                if(vars.size()==0){
                    return;
                }
                String sql=sb.toString();
                if(sql.endsWith(",")){
                    sql=sql.substring(0,sql.length()-1);
                }
                sb=new StringBuffer();
                sb.append(sql);
                sb.append(" where 1=1");
                for (String k : formDef.getPks().split(",")) {
                    sb.append(" and "+ObjectTools.camelToUnderline(k)+"=?");
                    Object d=params.get(ObjectTools.camelName(k));
                    vars.add(ObjectTools.isEmpty(d)?"":d);
                }
                template.update(sb.toString(),vars.toArray());
            }
        }else{//add
            if("2".equals(formDef.getDataType())){//寄存主表
                String dataField=ObjectTools.camelToUnderline(formDef.getDataField());
                String dataFieldCamle=ObjectTools.camelName(formDef.getDataField());
                StringBuffer sb=new StringBuffer();
                List<FormField> fields= FormDefService.calcDbFields(formFields);
                sb.append("insert ");
                sb.append(formDef.getTableName());
                sb.append(" ( "+dataField+","+formDef.getPks());
                sb.append(" ) value ( ?");
                List<Object> vars=new ArrayList<>();
                for (String k : formDef.getPks().split(",")) {
                    sb.append(",?");
                    Object d=params.get(ObjectTools.camelName(k));
                    vars.add(ObjectTools.isEmpty(d)?"":d);
                }
                sb.append(")");
                vars.add(0,params.get(dataFieldCamle));
                template.update(sb.toString(),vars.toArray());
            }else if("3".equals(formDef.getDataType())){//独立建表
                StringBuffer sb=new StringBuffer();
                List<Object> varList=new ArrayList<>();
                List<FormField> fields=FormDefService.calcDbFields(formFields);
                sb.append("insert ");
                sb.append(formDef.getTableName());
                sb.append("(");
                for (int i = 0; i < fields.size(); i++) {
                    FormField field=fields.get(i);
                    sb.append(field.getId());
                    if(i< fields.size()-1){
                        sb.append(",");
                    }
                }
                sb.append(")value(");
                for (int i = 0; i < fields.size(); i++) {
                    FormField field=fields.get(i);
                    sb.append("?");
                    if(i< fields.size()-1){
                        sb.append(",");
                    }

                    Object d=params.get(field.getIdCamel());
                    varList.add(ObjectTools.isEmpty(d)?"":d);
                }
                sb.append(")");
                template.update(sb.toString(),varList.toArray());
            }
        }
    }

    public Result list(FormDef formDef,List<FormField> fields,Map<String,Object> params) {
        DynamicDataSourceContextHolder.push(formDef.getTableOwner());

        QueryWrapper<DbTable> qw=QueryTools.initQueryWrapper(DbTable.class,params);
        IPage page=QueryTools.initPage(params);
        params.put("tableName",formDef.getTableName());
        List<Map<String,Object>> datas=baseMapper.selectListMapByWhere(page,qw,params);
        DynamicDataSourceContextHolder.poll();
        return Result.ok().setData(datas).setTotal(page.getTotal());
    }


    public String getStr(Object val){
        if(val==null){
            return "";
        }else if(val instanceof Integer){
            return Integer.toString((Integer) val);
        }else if(val instanceof Long){
            return Long.toString((Long) val);
        }else if(val instanceof Short){
            return Short.toString((Short) val);
        }else if(val instanceof Float){
            return Float.toString((Float) val);
        }else if(val instanceof Double){
            return Double.toString((Double) val);
        }else if(val instanceof BigDecimal){
             return ((BigDecimal)(val)).toString();
        }else if(val instanceof Date){
            return DateUtils.format((Date)val,DateUtils.datetimePattern);
        }else if(val instanceof String){
            return (String)val;
        }else{
            return val.toString();
        }
    }
}
